﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ERP.Manager.Core;
using ERP.Manager.DataEntity;
using ERP.Manager.DataProvider;
using ERP.Manager.DataHelper;
using System.Data.SqlClient;
using System.Data;
namespace ERP.Manager.SQLServerProvider
{
    public class ECS_goodsinfoRepository : IECS_goodsinfoRepository
    {

        ECS_goods_attrinfoRepository ecs_goods_attrinforepository = new ECS_goods_attrinfoRepository();
        ECS_goods_typeinfoRepository ecs_goods_typeinfoprpository = new ECS_goods_typeinfoRepository();

        #region 添加商品信息
        public int add_ecs_goods(ECS_goodsinfo ecs_goods)
        {
            int rowcount = SQLHelper.ExecuteNonQuery(CommandType.Text, ERPSQL.ADD_ECS_GOODS,
                    new SqlParameter("@cat_id", ecs_goods.ecs_categoryinfo.cat_id),
                    new SqlParameter("@goods_sn", ecs_goods.goods_sn),
                    new SqlParameter("@goods_name", ecs_goods.goods_name),
                    new SqlParameter("@brand_id", ecs_goods.ecs_brandinfo.brand_id),
                    new SqlParameter("@goods_number", ecs_goods.goods_number),
                    new SqlParameter("@market_price", ecs_goods.market_price),
                    new SqlParameter("@warn_number", ecs_goods.warn_number),
                    new SqlParameter("@keywords", ecs_goods.keywords),
                    new SqlParameter("@goods_brief", ecs_goods.goods_brief),
                    new SqlParameter("@goods_desc", ecs_goods.goods_desc),
                    new SqlParameter("@goods_img", ecs_goods.goods_img),
                    new SqlParameter("@is_on_sale", ecs_goods.is_on_sale),
                    new SqlParameter("@is_alone_sale", ecs_goods.is_alone_sale),
                    new SqlParameter("@add_time", ecs_goods.add_time),
                    new SqlParameter("@is_delete", ecs_goods.is_delete),
                    new SqlParameter("@is_best", ecs_goods.is_best),
                    new SqlParameter("@is_new", ecs_goods.is_new),
                    new SqlParameter("@is_hot", ecs_goods.is_hot),
                    new SqlParameter("@goods_type", ecs_goods.ecs_goods_typeinfo.cat_id),
                    new SqlParameter("@seller_note", ecs_goods.seller_note));
            return rowcount;
        }
        #endregion

        #region 把商品放入到回收站中
        public int delete_ecs_goods(int goods_id)
        {
            int rowcount = SQLHelper.ExecuteNonQuery(CommandType.Text, ERPSQL.DELETE_ECS_GOODS, new SqlParameter("@goods_id", goods_id));
            return rowcount;
        }
        #endregion

        #region 删除商品信息的方法
        public int delete_ecs_goods_remove(int goods_id)
        {
            int rowcount = SQLHelper.ExecuteNonQuery(CommandType.Text, ERPSQL.DELETE_ECS_GOODS_REMOVE,
               new SqlParameter("@goods_id", goods_id));
            return rowcount;
        }
        #endregion

        #region 修改商品库存的数量的方法
        public int update_ecs_goods_number(int goods_id, int goods_number)
        {
            int rowcount = SQLHelper.ExecuteNonQuery(CommandType.Text, ERPSQL.UPDATE_ECS_GOODS_NUMBER,
                new SqlParameter("@goods_id", goods_id),
                new SqlParameter("@goods_number", goods_number));
            return rowcount;
        }
        #endregion

        #region 修改商品信息
        public int update_ecs_goods(ECS_goodsinfo ecs_goods)
        {
            int rowcount = SQLHelper.ExecuteNonQuery(CommandType.Text, ERPSQL.UPDATE_ECS_GOODS,
                   new SqlParameter("@goods_id", ecs_goods.goods_id),
                   new SqlParameter("@cat_id", ecs_goods.ecs_categoryinfo.cat_id),
                   new SqlParameter("@goods_sn", ecs_goods.goods_sn),
                   new SqlParameter("@goods_name", ecs_goods.goods_name),
                   new SqlParameter("@brand_id", ecs_goods.ecs_brandinfo.brand_id),
                   new SqlParameter("@goods_number", ecs_goods.goods_number),
                   new SqlParameter("@market_price", ecs_goods.market_price),
                   new SqlParameter("@warn_number", ecs_goods.warn_number),
                   new SqlParameter("@keywords", ecs_goods.keywords),
                   new SqlParameter("@goods_brief", ecs_goods.goods_brief),
                   new SqlParameter("@goods_desc", ecs_goods.goods_desc),
                   new SqlParameter("@goods_img", ecs_goods.goods_img),
                   new SqlParameter("@is_on_sale", ecs_goods.is_on_sale),
                   new SqlParameter("@is_alone_sale", ecs_goods.is_alone_sale),
                   new SqlParameter("@is_best", ecs_goods.is_best),
                   new SqlParameter("@is_new", ecs_goods.is_new),
                   new SqlParameter("@is_hot", ecs_goods.is_hot),
                   new SqlParameter("@goods_type", ecs_goods.ecs_goods_typeinfo.cat_id),
                   new SqlParameter("@seller_note", ecs_goods.seller_note));
            return rowcount;
        }
        #endregion

        #region 还原商品的方法
        public int update_ecs_goodsinfo_remove(int goods_id)
        {
            int rowcount = SQLHelper.ExecuteNonQuery(CommandType.Text, ERPSQL.UPDATE_ECS_GOODSINFO_REMOVE,
                new SqlParameter("@goods_id", goods_id));
            return rowcount;
        }
        #endregion

        #region 根据商品分类编号查询商品的所有记录
        public List<ECS_goodsinfo> all_ecs_goodsinfo_category_cat_id_page(int pagecount, int cat_id)
        {
            List<ECS_goodsinfo> list = new List<ECS_goodsinfo>();
            SqlDataReader read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.ALL_ECS_GOODSINFO_CATEGORY_CAT_ID_PAGE,
                new SqlParameter("@pagecount", pagecount),
                new SqlParameter("@cat_id", cat_id));
            try
            {
                while (read.Read())
                {
                    string attr_value = "";
                    ECS_goodsinfo goods = new ECS_goodsinfo();
                    goods.goods_id = int.Parse(read["goods_id"].ToString());
                    ECS_goods_typeinfo ecs_goods_typeinfo = ecs_goods_typeinfoprpository.detail_ecs_goods_typeinfo(int.Parse(read["goods_type"].ToString()));
                    goods.ecs_goods_typeinfo.cat_id = ecs_goods_typeinfo.cat_id;
                    goods.ecs_goods_typeinfo.cat_name = ecs_goods_typeinfo.cat_name;
                    goods.goods_name = read["goods_name"].ToString();
                    goods.goods_sn = read["goods_sn"].ToString();
                    goods.market_price = decimal.Parse(read["market_price"].ToString());
                    List<ECS_goods_attrinfo> attr_values = ecs_goods_attrinforepository.all_ecs_goods_attrinfo(int.Parse(read["goods_id"].ToString()));
                    if (attr_values.Count > 0)
                    {
                        foreach (var item in attr_values)
                        {
                            attr_value += item.attr_value + "，";
                        }
                        int i = attr_value.Length;
                        attr_value = attr_value.Substring(0, i - 1);
                    }
                    goods.attr_value = attr_value;
                    goods.goods_number = int.Parse(read["goods_number"].ToString());
                    goods.add_time = DateTime.Parse(read["add_time"].ToString());
                    list.Add(goods);
                }
                read.Close();
            }
            catch (Exception)
            {
                read.Close();
            }
            finally
            {
                read.Dispose();
            }
            return list;
        }
        #endregion

        #region 分页查询所有商品信息
        public List<ECS_goodsinfo> all_ecs_goodsinfo_page(int pagecount)
        {
            List<ECS_goodsinfo> list = new List<ECS_goodsinfo>();
            SqlDataReader read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.ALL_ECS_GOODSINFO_PAGE,
                new SqlParameter("@pagecount", pagecount));
            try
            {
                while (read.Read())
                {
                    string attr_value = "";
                    ECS_goodsinfo goods = new ECS_goodsinfo();
                    goods.goods_id = int.Parse(read["goods_id"].ToString());
                    ECS_goods_typeinfo ecs_goods_typeinfo = ecs_goods_typeinfoprpository.detail_ecs_goods_typeinfo(int.Parse(read["goods_type"].ToString()));
                    goods.ecs_goods_typeinfo.cat_id = ecs_goods_typeinfo.cat_id;
                    goods.ecs_goods_typeinfo.cat_name = ecs_goods_typeinfo.cat_name;
                    goods.goods_name = read["goods_name"].ToString();
                    goods.goods_sn = read["goods_sn"].ToString();
                    goods.market_price = decimal.Parse(read["market_price"].ToString());
                    List<ECS_goods_attrinfo> attr_values = ecs_goods_attrinforepository.all_ecs_goods_attrinfo(int.Parse(read["goods_id"].ToString()));
                    if (attr_values.Count > 0)
                    {
                        foreach (var item in attr_values)
                        {
                            attr_value += item.attr_value + "，";
                        }
                        int i = attr_value.Length;
                        attr_value = attr_value.Substring(0, i - 1);
                    }
                    goods.attr_value = attr_value;
                    goods.goods_number = int.Parse(read["goods_number"].ToString());
                    goods.add_time = DateTime.Parse(read["add_time"].ToString());
                    list.Add(goods);
                }
                read.Close();
            }
            catch (Exception)
            {
                read.Close();
            }
            finally
            {
                read.Dispose();
            }
            return list;
        }
        #endregion

        #region 查询所有商品信息
        public List<ECS_goodsinfo> all_ecs_goodsinfo()
        {
            List<ECS_goodsinfo> list = new List<ECS_goodsinfo>();
            SqlDataReader read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.ALL_ECS_GOODSINFO);
            try
            {
                while (read.Read())
                {
                    ECS_goodsinfo goods = new ECS_goodsinfo();
                    goods.goods_id = int.Parse(read["goods_id"].ToString());
                    goods.ecs_goods_typeinfo.cat_id = int.Parse(read["goods_type"].ToString());
                    goods.goods_name = read["goods_name"].ToString();
                    goods.goods_sn = read["goods_sn"].ToString();
                    goods.market_price = decimal.Parse(read["market_price"].ToString());
                    goods.goods_number = int.Parse(read["goods_number"].ToString());
                    goods.add_time = DateTime.Parse(read["add_time"].ToString());
                    list.Add(goods);
                }
                read.Close();
            }
            catch (Exception)
            {
                read.Close();
            }
            finally
            {
                read.Dispose();
            }
            return list;
        }
        #endregion

        #region 查询商品详细信息
        public ECS_goodsinfo detail_ecs_goods(int goods_id)
        {
            ECS_goodsinfo ecs_goods = new ECS_goodsinfo();
            SqlDataReader read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.DETAIL_ECS_GOODS, new SqlParameter("@goods_id", goods_id));
            try
            {
                while (read.Read())
                {
                    ecs_goods.goods_id = int.Parse(read["goods_id"].ToString());
                    ecs_goods.ecs_categoryinfo.cat_id = int.Parse(read["cat_id"].ToString());
                    ecs_goods.goods_sn = read["goods_sn"].ToString();
                    ecs_goods.goods_name = read["goods_name"].ToString();
                    ecs_goods.ecs_brandinfo.brand_id = int.Parse(read["brand_id"].ToString());
                    ecs_goods.goods_number = int.Parse(read["goods_number"].ToString());
                    ecs_goods.market_price = decimal.Parse(read["market_price"].ToString());
                    ecs_goods.warn_number = int.Parse(read["warn_number"].ToString());
                    ecs_goods.keywords = read["keywords"].ToString();
                    ecs_goods.goods_brief = read["goods_brief"].ToString();
                    ecs_goods.goods_desc = read["goods_desc"].ToString();
                    ecs_goods.goods_img = read["goods_img"].ToString();
                    ecs_goods.is_on_sale = int.Parse(read["is_on_sale"].ToString());
                    ecs_goods.is_alone_sale = int.Parse(read["is_alone_sale"].ToString());
                    ecs_goods.add_time = DateTime.Parse(read["add_time"].ToString());
                    ecs_goods.is_delete = int.Parse(read["is_delete"].ToString());
                    ecs_goods.is_best = int.Parse(read["is_best"].ToString());
                    ecs_goods.is_new = int.Parse(read["is_new"].ToString());
                    ecs_goods.is_hot = int.Parse(read["is_hot"].ToString());
                    ecs_goods.ecs_goods_typeinfo.cat_id = int.Parse(read["goods_type"].ToString());
                    ecs_goods.seller_note = read["seller_note"].ToString();
                }
                read.Close();
            }
            catch (Exception)
            {
                read.Close();
            }
            finally
            {
                read.Dispose();
            }
            return ecs_goods;
        }
        #endregion

        #region 分页查询所有假删除的商品信息的方法
        public List<ECS_goodsinfo> all_ecs_goodsinfo_remove_page(int pagecount)
        {
            List<ECS_goodsinfo> list = new List<ECS_goodsinfo>();
            SqlDataReader read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.ALL_ECS_GOODSINFO_REMOVE_PAGE,
                new SqlParameter("@pagecount", pagecount));
            try
            {
                while (read.Read())
                {
                    ECS_goodsinfo goods = new ECS_goodsinfo();
                    goods.goods_id = int.Parse(read["goods_id"].ToString());
                    goods.goods_name = read["goods_name"].ToString();
                    goods.goods_sn = read["goods_sn"].ToString();
                    goods.market_price = decimal.Parse(read["market_price"].ToString());
                    goods.goods_number = int.Parse(read["goods_number"].ToString());
                    list.Add(goods);
                }
                read.Close();
            }
            catch (Exception)
            {
                read.Close();
            }
            finally
            {
                read.Dispose();
            }
            return list;
        }
        #endregion

        #region 搜索 根据品牌、商品名称查询商品信息
        public List<ECS_goodsinfo> search_all_ecs_goodsinfo_page(int pagecount, int brand_id, string goods_name, string begin_time, string end_time)
        {
            List<ECS_goodsinfo> list = new List<ECS_goodsinfo>();
            SqlDataReader read = null;
            if (begin_time == "" || end_time == "")
            {
                read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.SEARCH_ALL_ECS_GOODSINFO_BRAND_ID_AND_GOODS_NAME_PAGE,
                  new SqlParameter("@pagecount", pagecount),
                  new SqlParameter("@brand_id", brand_id),
                  new SqlParameter("@goods_name", "%" + goods_name + "%"));
            }
            else if (brand_id == 0 || goods_name == "")
            {
                read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.SEARCH_ALL_ECS_GOODSINFO_ADD_TIME_PAGE,
                new SqlParameter("@pagecount", pagecount),
                new SqlParameter("@begin_time", begin_time),
                new SqlParameter("@end_time", end_time));
            }
            else
            {
                read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.SEARCH_ALL_ECS_GOODSINFO_BRAND_ID_AND_GOODS_NAME_AND_ADD_TIME_PAGE,
               new SqlParameter("@pagecount", pagecount),
               new SqlParameter("@begin_time", begin_time),
               new SqlParameter("@end_time", end_time),
               new SqlParameter("@brand_id", brand_id),
               new SqlParameter("@goods_name", "%" + goods_name + "%"));
            }
            try
            {
                while (read.Read())
                {
                    string attr_value = "";
                    ECS_goodsinfo goods = new ECS_goodsinfo();
                    goods.goods_id = int.Parse(read["goods_id"].ToString());
                    ECS_goods_typeinfo ecs_goods_typeinfo = ecs_goods_typeinfoprpository.detail_ecs_goods_typeinfo(int.Parse(read["goods_type"].ToString()));
                    goods.ecs_goods_typeinfo.cat_id = ecs_goods_typeinfo.cat_id;
                    goods.ecs_goods_typeinfo.cat_name = ecs_goods_typeinfo.cat_name;
                    goods.goods_name = read["goods_name"].ToString();
                    goods.goods_sn = read["goods_sn"].ToString();
                    List<ECS_goods_attrinfo> attr_values = ecs_goods_attrinforepository.all_ecs_goods_attrinfo(int.Parse(read["goods_id"].ToString()));
                    if (attr_values.Count > 0)
                    {
                        foreach (var item in attr_values)
                        {
                            attr_value += item.attr_value + "，";
                        }
                        int i = attr_value.Length;
                        attr_value = attr_value.Substring(0, i - 1);
                    }
                    goods.attr_value = attr_value;
                    goods.market_price = decimal.Parse(read["market_price"].ToString());
                    goods.goods_number = int.Parse(read["goods_number"].ToString());
                    goods.add_time = DateTime.Parse(read["add_time"].ToString());
                    list.Add(goods);
                }
                read.Close();
            }
            catch (Exception)
            {
                read.Close();
            }
            finally
            {
                read.Dispose();
            }
            return list;
        }
        #endregion

        #region 根据商品分类编号查询商品的所有记录的条数
        public int all_ecs_goodsinfo_category_cat_id_page_count(int cat_id)
        {
            var rowcount = SQLHelper.ExecuteScalar(CommandType.Text, ERPSQL.ALL_ECS_GOODSINFO_CATEGORY_CAT_ID_PAGE_COUNT, new SqlParameter("@cat_id", cat_id));
            return int.Parse(rowcount.ToString());
        }
        #endregion

        #region 查询所有商品信息的所有记录条数
        public int all_ecs_goodsinfo_page_count()
        {
            var rowcount = SQLHelper.ExecuteScalar(CommandType.Text, ERPSQL.ALL_ECS_GOODSINFO_PAGE_COUNT);
            return int.Parse(rowcount.ToString());
        }
        #endregion

        #region 查询商品被删除的记录条数的方法
        public int all_ecs_goodsinfo_remove_page_count()
        {
            var rowcount = SQLHelper.ExecuteScalar(CommandType.Text, ERPSQL.ALL_ECS_GOODSINFO_REMOVE_PAGE_COUNT);
            return int.Parse(rowcount.ToString());
        }
        #endregion

        #region 根据查询品牌、商品名称得到总共记录
        public int search_all_ecs_goodsinfo_page_count(int brand_id, string goods_name, string begin_time, string end_time)
        {
            object rowcount = null;
            if (begin_time == "" || end_time == "")
            {
                rowcount = SQLHelper.ExecuteScalar(CommandType.Text, ERPSQL.SEARCH_ALL_ECS_GOODSINFO_PAGE_COUNT,
                   new SqlParameter("@brand_id", brand_id),
                   new SqlParameter("@goods_name", "%" + goods_name + "%"));
            }
            else if (brand_id == 0 || goods_name == "")
            {
                rowcount = SQLHelper.ExecuteScalar(CommandType.Text, ERPSQL.SEARCH_ALL_ECS_GOODSINFO_ADD_TIME_PAGE_COUNT,
                new SqlParameter("@begin_time", begin_time),
               new SqlParameter("@end_time", end_time));
            }
            else
            {
                rowcount = SQLHelper.ExecuteScalar(CommandType.Text, ERPSQL.SEARCH_ALL_ECS_GOODSINFO_BRAND_ID_AND_GOODS_NAME_AND_ADD_TIME_PAGE_count,
                     new SqlParameter("@brand_id", brand_id),
                   new SqlParameter("@goods_name", "%" + goods_name + "%"),
                new SqlParameter("@begin_time", begin_time),
               new SqlParameter("@end_time", end_time));
            }
            return int.Parse(rowcount.ToString());
        }
        #endregion
    }
}
